prompt PL/SQL Developer import file
prompt Created on Wednesday, August 22, 2012 by ffwgvp
set feedback off
set define off
prompt Creating MEMBER_DETAILS...
create table MEMBER_DETAILS
(
  MEMBER_ID    NUMBER not null,
  SPONSOR_ID   NUMBER,
  GROUP_ID     VARCHAR2(4000),
  FIRST_NAME   VARCHAR2(30),
  MIDDLE_NAME  VARCHAR2(30),
  LAST_NAME    VARCHAR2(30),
  ADDRESS      VARCHAR2(4000),
  ZIPCODE      VARCHAR2(5),
  CONTACT_NO   VARCHAR2(40),
  BIRTHDATE    DATE,
  AGE          VARCHAR2(3),
  SEX          VARCHAR2(2),
  STATUS       VARCHAR2(2),
  NATIONALITY  VARCHAR2(20),
  DATE_CREATED DATE,
  DATE_UPDATED DATE
)
;
alter table MEMBER_DETAILS
  add constraint MEMBER_DETAILS_PK primary key (MEMBER_ID);

prompt Loading MEMBER_DETAILS...
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (4, 3, null, 'PAOPAO', 'GAPAZ', 'ZAMORA', 'SAN PABLO CITY', '12345', '09175454655', to_date('19-08-2012', 'dd-mm-yyyy'), '28', 'F', 'M', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (5, 3, null, 'ISABEL', 'CHICHI', 'ZAMORA', 'SAN LORENZO CITY', '12345', '09195556677', to_date('19-08-2012', 'dd-mm-yyyy'), '28', 'F', 'M', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (6, 7, null, 'GLENN', 'RICARDO', 'AVILA', 'SAN MARTIN CITY', '12345', '09195556644', to_date('19-08-2012', 'dd-mm-yyyy'), '28', 'F', 'M', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (7, 5, null, 'MARIA', 'CYNTHIA', 'BARGAS', 'ISALA PUNO NG TUBIG', '12345', '09195556223', to_date('19-08-2012', 'dd-mm-yyyy'), '24', 'F', 'S', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (8, 9, null, 'DOLORES', 'NINA', 'CASPI', 'MALYONG DAGAT', '12345', '09195556223', to_date('19-08-2012', 'dd-mm-yyyy'), '24', 'F', 'S', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (9, 2, null, 'RAFAEL', 'QUINTO', 'ISAGANI', 'DAPITAN TAN', '12345', '09195556223', to_date('19-08-2012', 'dd-mm-yyyy'), '24', 'M', 'S', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (22, null, null, 'ROOT', 'ROOT', 'ROOT', 'ROOT', '11111', '09189999999', to_date('19-08-2012', 'dd-mm-yyyy'), '200', 'M', 'S', 'FILIPINO', to_date('19-08-2012', 'dd-mm-yyyy'), to_date('19-08-2012', 'dd-mm-yyyy'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (2, 1, 'R', 'JUAN PAULO', 'TATARO', 'QUILALA', 'CALAMBA CITY', '12345', '09173009049', to_date('19-08-2012 11:34:11', 'dd-mm-yyyy hh24:mi:ss'), '28', 'M', 'M', 'FILIPINO', to_date('19-08-2012 11:34:11', 'dd-mm-yyyy hh24:mi:ss'), to_date('19-08-2012 11:34:11', 'dd-mm-yyyy hh24:mi:ss'));
insert into MEMBER_DETAILS (MEMBER_ID, SPONSOR_ID, GROUP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, ZIPCODE, CONTACT_NO, BIRTHDATE, AGE, SEX, STATUS, NATIONALITY, DATE_CREATED, DATE_UPDATED)
values (3, 1, 'R', 'MELODY', 'GAPAZ', 'QUILALA', 'SAN PABLO CITY', '12345', '09175454655', to_date('19-08-2012 11:35:32', 'dd-mm-yyyy hh24:mi:ss'), '28', 'F', 'M', 'FILIPINO', to_date('19-08-2012 11:35:32', 'dd-mm-yyyy hh24:mi:ss'), to_date('19-08-2012 11:35:32', 'dd-mm-yyyy hh24:mi:ss'));
commit;
prompt 9 records loaded
set feedback on
set define on
prompt Done.


-- Create sequence 
create sequence MEMBER_ID_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 42
increment by 1
cache 20;


create or replace trigger "BI_MEMBER_DETAILS"   
  before insert on "MEMBER_DETAILS"               
  for each row  
begin   
    select "MEMBER_ID_SEQ".nextval into :NEW.MEMBER_ID from dual; 
end;
